package com.davinci.freemarket.dataAccessLayer.accessdata.user;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.davinci.freemarket.businessLayer.utils.DBInfoUtil;
import com.davinci.freemarket.dataAccessLayer.accessdata.DataBaseConnection;
import com.davinci.freemarket.dataModelLayer.model.DBAttibuteModel;
import com.davinci.freemarket.dataModelLayer.model.UserModel;
import com.microsoft.sqlserver.jdbc.SQLServerException;

/**
 * Esta clase controla el acceso a datos del usuario
 * permite el listado buscando por parametros
 * 
 * @author Lucas Buyo
 */
public class UserDAO 
{
	/**
	 * 
	 * */
	protected DataBaseConnection dbcnx;
	
	/**
	 * 
	 * */
	protected Connection conn;
	
	
	//-------------------
	// CONSTRUCTOR
	//-------------------
	public UserDAO() {
		this.dbcnx = new DataBaseConnection();
	}
	
	/**
	 * Controla el acceso del usuario al sistema
	 * 
	 * @param user
	 * @return
	 * @throws SQLException 
	 */
	public UserModel login(UserModel user) throws SQLException
	{
		ArrayList<DBAttibuteModel> conditions = new ArrayList<DBAttibuteModel>();
		conditions.add(new DBAttibuteModel("EMAIL", user.getEmail()));
		conditions.add(new DBAttibuteModel("PASS", user.getPass()));
		
		ResultSet rs = this.selectUsers(conditions, null);
		
		/*
		String result = "";
		while (rs.next()) {
			result += rs.getString(1) + ", " + rs.getString(2) + ", " +rs.getString(3) + ", " +rs.getString(4) + ", " +rs.getString(5);
		}
		System.out.println(result);
		*/
		
		if (rs.next()) {
			user.setUserID(rs.getString("ID_USER"));
			user.setNick(rs.getString("NICK"));
			user.setPass("");
			user.setEmail(rs.getString("EMAIL"));
			user.setAddress(rs.getString("DIRECCION"));
			user.setName(rs.getString("NOMBRE"));
			user.setLastname(rs.getString("APELLIDO"));
			user.setPhone(rs.getString("TELEFONO"));
			user.setUserValid(true);
		}
		System.out.println(user);
		
		return user;
	}
	
	/**
	 * Selecciona usuario de la tabla segun cumplan la condicion
	 * 
	 * @param conditions
	 * @return
	 */
	public ResultSet selectUsers(ArrayList<DBAttibuteModel> conditions, String operator){
//		String sql = "SELECT * FROM [davinci_taller3].[dbo].[USUARIO] where PASS like '%pass%'";
		conn = dbcnx.getConnection();
		PreparedStatement statement = null;
		ResultSet rs = null;
		String sqlQuery = "";
		
		Boolean firstCondition = true;
		if(operator == null)
			operator = "=";
		
		try {
			sqlQuery = "SELECT [ID_USER] ,[NICK] ,[EMAIL] ,[DIRECCION] ,[NOMBRE] ,[APELLIDO] ,[TELEFONO] " +
						"FROM ["+DBInfoUtil.DAVINCI_DATABASE+"].[dbo].["+DBInfoUtil.USER_TABLE+"] " +
					  	"WHERE";
			
			//-- CON LAS SIGUIENTES PROPIEDADES = {ATTRIBUTE, VALUE}
			for (DBAttibuteModel object : conditions) {
				sqlQuery += firstCondition ? " " + object.column + " "+operator+" ?" : " AND " + object.column + " "+operator+" ?"; 
				firstCondition = false;
			}
			
			statement = conn.prepareStatement(sqlQuery);
			
			int index = 1;
			for (DBAttibuteModel dbAttibuteModel : conditions) {
				statement.setString(index, dbAttibuteModel.value);
				index++;
			}
			
			rs = statement.executeQuery();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
//		closeConnection();
		return rs;	
	}
	
	/**
	 * Agrega un nuevo usuario en la tabla USUARIO
	 * 
	 * @param nick
	 * @param pass
	 * @param email
	 * @param address
	 * @param name
	 * @param lastname
	 * @param phone
	 * @return
	 */
	public int addUser(String nick, String pass, String email, String address, String name, String lastname, String phone){
//		String sql = "INSERT INTO [davinci_taller3].[dbo].[USUARIO] VALUES ('Lucas' ,'Muma' ,'lucasbuyo@gmail.com' ,'direccion 123' ,'Lucas' ,'Buyo' ,1324);";
		conn = dbcnx.getConnection();
		PreparedStatement statement = null; 
        int rs = 0;
        
		try {
			statement = conn.prepareStatement("INSERT INTO ["+DBInfoUtil.DAVINCI_DATABASE+"].[dbo].["+DBInfoUtil.USER_TABLE+"] " +
											  	"VALUES (? ,? ,? ,? ,? ,? ,?);");
			statement.setString(1, nick);
			statement.setString(2, pass);
			statement.setString(3, email);
			statement.setString(4, address);
			statement.setString(5, name);
			statement.setString(6, lastname);
			statement.setString(7, phone);
			
			rs = statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
		closeConnection();
		return rs;
	} 
	
	/**
	 * Modifica un usuario en la tabla USUARIO
	 * 
	 * @param user_id
	 * @param nick
	 * @param pass
	 * @param email
	 * @param address
	 * @param name
	 * @param lastname
	 * @param phone
	 * @return
	 */
	public int modifyUser(int user_id, String nick, String pass, String email, String address, String name, String lastname, String phone){
//		String sql = "update davinci_taller3.dbo.USUARIO set NICK = 'Mario', PASS = '123', EMAIL = 'unmail' where ID_USER > 0;";
		conn = dbcnx.getConnection();
		PreparedStatement statement = null;
        int rs = 0;
        
		try {
			statement = conn.prepareStatement("UPDATE ["+DBInfoUtil.DAVINCI_DATABASE+"].[dbo].["+DBInfoUtil.USER_TABLE+"] " +
												"SET NICK = ?, PASS = ?, EMAIL = ?, DIRECCION = ?, NOMBRE = ?, APELLIDO = ?, TELEFONO = ? " +
											  	"WHERE ID_USER = ?;");
			statement.setString(1, nick);
			statement.setString(2, pass);
			statement.setString(3, email);
			statement.setString(4, address);
			statement.setString(5, name);
			statement.setString(6, lastname);
			statement.setString(7, phone);
			statement.setInt(8, user_id);
			
			rs = statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	
		closeConnection();
		return rs;
	}
	
	/**
	 * Eliminar un usuario de la tabla
	 * @param user_id
	 * @return
	 */
	public int deleteUser(int user_id){
//		String sql = "delete from davinci_taller3.dbo.USUARIO where ID_USER > 0;";
		conn = dbcnx.getConnection();
		PreparedStatement statement = null;
		int rs = 0;
		
		try {
			statement = conn.prepareStatement("DELETE FROM ["+DBInfoUtil.DAVINCI_DATABASE+"].[dbo].["+DBInfoUtil.USER_TABLE+"] " +
												"WHERE ID_USER = ?;");
			statement.setInt(1, user_id);
			
			rs = statement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		closeConnection();
		return rs;
	}
	
	/**
	 * 
	 * */
    public void closeConnection() {
        try {
            if (conn != null)
                conn.close();
            conn = null;
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
	
	
	

}
